﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule.MasterData
{
    public class ChainManager : BusinessLogic
    {

        public void DelteOrgsByChainID(int chainID)
        {
            SqlData.ExecuteSql("delete CHAINS_ORG where CHAIN_ID='" + chainID + "'");
        }


        public void DeleteChainsORG(int chainid, int orgID)
        {
            SqlData.ExecuteSql("delete CHAINS_ORG where CHAIN_ID='" + chainid + "' and ORG_ID='" + orgID + "'");
            SqlData.ExecuteSql("delete CUSTOMER_CHAINS where CHAINS_ID='" + chainid + "' and CUSTOMER_ID in (select id from CUSTOMERS where org_id='" + orgID + "')");
        }

        public DataTable LoadOrg(int chainID)
        {
            string sql = @"select	CHAINS.ID
		                            ,CHAINS.ORG_ID
		                            ,ORGANIZATION.NAME
                            from CHAINS
	                            inner join ORGANIZATION
		                            on CHAINS.ORG_ID=ORGANIZATION.ID
                            where CHAINS.ID='" + chainID + "'";

            return SqlData.OpenSql(sql).Tables[0];
        }

        public static void AssignChainForCustomer(bool insertFlag, bool updateFlag, decimal CCID, CUSTOMER_CHAINS customerChains, decimal domainID)
        {
            Jumpton.Data.SqlData.OpenSP("[sp_AssignChainsForCustomer]", insertFlag,
                                                        updateFlag,
                                                        CCID,
                                                        customerChains.CHAINSID,
                                                        customerChains.CUSTOMERID,
                                                        customerChains.LAST_UPDATER,
                                                        customerChains.LAST_UPDATE_TIME,
                                                        customerChains.SUPPLYID == 0 ? null : customerChains.SUPPLYID.ToString(),
                                                        domainID);
        }

        public DataTable GetDistribution(decimal customerID)
        {
            return Jumpton.Data.SqlData.OpenSql(string.Format("select * from CUSTOMER_CHAINS where customer_id='{0}'", customerID)).Tables[0];
        }

        public static DataTable SearchChainsForCustomer(decimal customerID, string Assigned, string strCondition)
        {
            return Jumpton.Data.SqlData.OpenSP("[usp_SelectChainsForCustomer]", customerID, Assigned, strCondition).Tables[0];
        }

        public DataTable GetChainsList(string code, string name, string orgID, int status)
        {
            string sql = @"select	distinct
                                    CHAINS.ID
                                    ,CHAINS.CODE
                                    ,CHAINS.DESCRIPTION
                                    ,(CASE WHEN CHAINS_ORG.CHAIN_ID IS NULL THEN '0' ELSE '1' END) AS INCLUDE
                            from CHAINS
                                left join CHAINS_ORG
                                    on CHAINS_ORG.CHAIN_ID=CHAINS.ID and CHAINS_ORG.ORG_ID='" + orgID + @"'
                            where CHAINS.VALID=1 ";
            if (!string.IsNullOrEmpty(code))
                sql += " and CHAINS.CODE like '%" + code + "%'";
            if (!string.IsNullOrEmpty(name))
                sql += " and CHAINS.DESCRIPTION like '%" + name + "%'";
            if (status == 1)
                sql += " and CHAINS_ORG.CHAIN_ID is not null";
            if (status == 0)
                sql += " and CHAINS_ORG.CHAIN_ID is null";
            return SqlData.OpenSql(sql).Tables[0];
        }

        public ObjectTable GetCourseTable(decimal domainID)
        {
            return new ObjectTable(typeof(CHAINS), "this.DOMAINID='" + domainID + "'");
        }

        public DataTable GetChainsByUser(decimal UserID)
        {
            return SqlData.OpenSql(@"select distinct t1.* from chains t1,
                customer_chains t2, route_customers t3
                where t1.id=t2.chains_id and t2.customer_id=t3.customer_id
                and t3.user_id=" + UserID.ToString()).Tables[0];
        }

        public DataTable GetCourseTable(decimal domainID, int orgID)
        {
//            string sql = string.Format(@"select	DISTINCT CHAINS.ID
//		                                                ,CHAINS.CODE
//		                                                ,CHAINS.DESCRIPTION
//                                        from  CHAINS
//	                                            
//                                        where CHAINS.ORG_ID IN ({0}) AND CHAINS.DOMAIN_ID={1} AND CHAINS.ACTIVE = 1", orgID, domainID);

            string sql = string.Format(@"select	DISTINCT CHAINS.ID
		                                                ,CHAINS.CODE
		                                                ,CHAINS.DESCRIPTION
                                        from  CHAINS
	                                            
                                        where CHAINS.DOMAIN_ID={1} AND CHAINS.ACTIVE = 1", orgID, domainID);
            
            
            return SqlData.OpenSql(sql).Tables[0];
        }


        public DataTable GetCourseTable(decimal domainID, string orgID)
        {
            string sql = string.Format(@"select	DISTINCT CHAINS.ID
		                                                ,CHAINS.CODE
		                                                ,CHAINS.DESCRIPTION
                                        from  CHAINS
	                                            
                                        where CHAINS.ORG_ID IN ({0}) AND CHAINS.DOMAIN_ID={1} AND CHAINS.ACTIVE = 1", orgID, domainID);
            return SqlData.OpenSql(sql).Tables[0];
        }

        public CHAINS Load(int id)
        {
            return (CHAINS)ObjectRow.Load(typeof(CHAINS), string.Format("this.ID='{0}'", id));
        }

        public CUSTOMER_CHAINS LoadCustomerChain(decimal customerID,decimal chainID)
        {
            return (CUSTOMER_CHAINS)ObjectRow.Load(typeof(CUSTOMER_CHAINS), string.Format("this.CUSTOMERID='{0}' and this.CHAINSID='{1}'", customerID,chainID));
        }

        public CHAINS_ORG LoadChainOrg(int chainID, decimal orgID)
        {
            return (CHAINS_ORG)ObjectRow.Load(typeof(CHAINS_ORG), string.Format("this.CHAINID='{0}' and this.ORGID='{1}'", chainID, orgID));
        }

        public CHAINS LoadCode(string Code, decimal DomainID)
        {
            return (CHAINS)ObjectRow.Load(typeof(CHAINS), string.Format("this.CODE='{0}' AND this.DOMAINID='{1}'", Code, DomainID));
        }
        public CHAINS LoadName(string Name, decimal DomainID)
        {
            return (CHAINS)ObjectRow.Load(typeof(CHAINS), string.Format("this.DESCRIPTION='{0}' AND this.DOMAINID='{1}'", Name, DomainID));
        }
        public decimal Save(CHAINS chain)
        {
            if (chain != null)
            {
                try
                {
                    if (chain.State == DataRowState.Added)
                    {
                        if (LoadCode(chain.CODE, chain.DOMAINID) != null)
                            throw new Exception("该经销商代码已存在!");
                        if(LoadName(chain.DESCRIPTION, chain.DOMAINID)!=null)
                            throw new Exception("该公司名称已存在!");

                      
                    }

                    chain.Save();
                    return chain.ID;
                }
                catch (Exception err)
                {
                    message = err.Message; throw err;

                }
            }

            return 0;
        }

        public bool Delete(CHAINS banner)
        {
            bool Successful = false;
            if (banner != null)
            {
                try
                {
                    banner.Delete();
                    Successful = true;
                }
                catch (Exception err)
                {
                    message = err.Message; throw err;
                   
                }
            }

            return Successful;
        }

        public DataTable GetChainTable(decimal domainId, string orgId, string codeOrName, string valid)
        {
            codeOrName = codeOrName.Trim();
            if (!"".Equals(codeOrName))
            {
                codeOrName = codeOrName.Replace("'", "''");
            }
            orgId = orgId.Replace("'", "");
          
            return SqlData.OpenSP("up_GetChainTable", domainId,orgId, codeOrName, valid).Tables[0];
        }
        public DataTable GetChainTable1(decimal domainId, string orgIdes, string codeOrName, string valid)
        {
            codeOrName = codeOrName.Trim();
            if (!"".Equals(codeOrName))
            {
                codeOrName = codeOrName.Replace("'", "''");
            }

            return SqlData.OpenSP("up_SearchChains", domainId, orgIdes, codeOrName, valid).Tables[0];
        }
    }
}
